﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace myScheduler
{
    public class SQLcontrol
    {
        //http://www.daniweb.com/software-development/vbnet/code/216920/sql-in-vb.net
        public int SQLwrite(string SQLStr)
        {
            try
            {
                //Connect to SQL
                string ConnString = ConfigurationManager.AppSettings["SQLconnection"];
                SqlConnection SQLConn = new SqlConnection(ConnString); //The SQL Connection
                SqlCommand SQLCmd = new SqlCommand(); //The SQL Command
                SQLConn.Open(); //Open the connection

                //Write to SQL
                int affectedRows = -1;
                SQLCmd.Connection = SQLConn; //Sets the Connection to use with the SQL Command
                SQLCmd.CommandText = SQLStr; //Sets the SQL String
                affectedRows = SQLCmd.ExecuteNonQuery(); //Executes SQL Commands Non-Querys only

                //Close SQL Connection
                SQLConn.Close();
                return affectedRows;
            }
            catch
            {
                return -1;
            }
        }

        public DataSet SQLread(string query)
        {
            DataSet dsResults = new DataSet();
            SqlDataAdapter daAdap = new SqlDataAdapter();
            try
            {
                //Connect to SQL
                string ConnString = ConfigurationManager.AppSettings["SQLconnection"];
                SqlConnection SQLConn = new SqlConnection(ConnString);
                SqlCommand SQLCmd = new SqlCommand();
                SQLConn.Open();

                //Read from SQL
                SQLCmd.Connection = SQLConn;
                SQLCmd.CommandText = query;
                daAdap.SelectCommand = SQLCmd;
                daAdap.Fill(dsResults);

                //Close SQL Connection
                SQLConn.Close();
                return dsResults;
            }
            catch
            {
                return null;
            }
        }

       static  public DataSet SQLread2(string query)
        {
            DataSet dsResults = new DataSet();
            SqlDataAdapter daAdap = new SqlDataAdapter();
            try
            {
                //Connect to SQL
                string ConnString = ConfigurationManager.AppSettings["SQLconnection"];
                SqlConnection SQLConn = new SqlConnection(ConnString);
                SqlCommand SQLCmd = new SqlCommand();
                SQLConn.Open();

                //Read from SQL
                SQLCmd.Connection = SQLConn;
                SQLCmd.CommandText = query;
                daAdap.SelectCommand = SQLCmd;
                daAdap.Fill(dsResults);

                //Close SQL Connection
                SQLConn.Close();
                return dsResults;
            }
            catch
            {
                return null;
            }
        }
        static public List<string> SQLreadXML(string query)
        {
            string ConnString = ConfigurationManager.AppSettings["SQLconnection"];
            SqlConnection SQLConn = new SqlConnection(ConnString);
            SqlCommand SQLCmd = new SqlCommand();
            SqlDataReader reader;

            List<string> gotten = new List<string>();

            try
            {
                SQLConn.Open();

                SQLCmd.CommandText = query;
                //cmd.CommandType = CommandType.Text;
                SQLCmd.Connection = SQLConn;

                reader = SQLCmd.ExecuteReader();
                // Data is accessible through the DataReader object here.
                while (reader.Read())
                {
                    string xml_returned = reader[0].ToString();
                    gotten.Add(xml_returned);
                }
            }
            catch
            {
                gotten = null;
            }            
            
            SQLConn.Close();
            return gotten;
        }

        public string ReadSQLscript(string strScriptPath)
        {
            string strSQL = "";
            StreamReader oRead;
            try
            {
                oRead = System.IO.File.OpenText(HttpContext.Current.Server.MapPath("sql/") + strScriptPath);
                strSQL = oRead.ReadToEnd();
                oRead.Close();
            }
            catch
            {
                return "-1";
            }
            return strSQL;
        }
    }
}